import mysql.connector
from util import DbConnectUtils as db

# 全局的数据库的连接对象
con = db.con()

"""
    在 python中实现 创建表的操作
"""


def create_table():
    cursor = None
    try:
        sql = 'create table `python_0215`(' \
              'id int primary key not null comment \'id编号\',' \
              'sex int not null' \
              ');'
        #     游标 执行 sql
        cursor = con.cursor()
        cursor.execute(sql)
    except mysql.connector.errors:
        print('创建表失败')

    cursor.close()
    con.close()


"""
    模拟登录的sql注入问题
    会产生  sql  注入问题 
"""


def login(username, password):
    # 编写 sql语句
    sql = "SELECT * from python_test_table where name = '" + username + "' and password = '" + password + "'"
    print(sql)
    #     游标 执行 sql
    cursor = con.cursor()
    cursor.execute(sql)
    value = cursor.fetchall()
    print(value)
    con.close()


"""
    模拟登录的sql注入问题
    不会产生  sql  注入问题 
"""


def login_success(username, password):
    global con
    # 编写 sql语句
    sql = "SELECT * from python_test_table where name = %s and password = %s"
    print(sql)
    #     游标 执行 sql
    cursor = con.cursor()
    cursor.execute(sql, (username, password,))
    value = cursor.fetchall()
    print(value)
    con.close()


"""
    向数据库中表中添加数据 insert 单行添加
"""


def insert_only(index, sex):
    global con
    # 编写 sql语句
    sql = "insert into `python_0215`(`id`,`sex`) values (%s,%s)"  # %i
    #     游标 执行 sql
    cursor = con.cursor()
    cursor.execute(sql, (index, sex,))
    con.commit()
    cursor.close()
    con.close()


"""
    向数据库中表中添加数据 insert 多行插入
"""


def insert_many(data):
    global con
    # 编写 sql语句
    sql = "insert into `python_0215`(`id`,`sex`) values (%s,%s)"  # %i
    #     游标 执行 sql
    cursor = con.cursor()
    cursor.executemany(sql, data)
    con.commit()
    cursor.close()
    con.close()


def delete_by_id(by_id):
    global con
    # 编写 sql语句
    sql = "delete from python_0215 where id =%s"  # %i
    #     游标 执行 sql
    cursor = con.cursor()
    cursor.execute(sql, by_id)
    con.commit()
    cursor.close()
    con.close()


def update_by_id(by_id):
    global con
    # 编写 sql语句
    sql = "update python_0215 set sex = 0 where id =%s"  # %i
    #     游标 执行 sql
    cursor = con.cursor()
    cursor.execute(sql, by_id)
    con.commit()
    cursor.close()
    con.close()


"""
    事务
"""


def transaction(transferorId, payeeId, money):
    cursor = con.cursor()
    try:
        sql1 = "update python_test_table set money = mone - %s where id = %s"
        cursor.execute(sql1, (money, transferorId,))
        con.commit()
        sql2 = "update python_test_table set money = money + %s where id = %s"
        cursor.execute(sql2, (money, payeeId,))
        con.commit()
        print('成功')
    except:
        print('失败')
        con.rollback()
    finally:
        cursor.close()
        con.close()


if __name__ == '__main__':
    # login('hanamaki', '123456')
    # login(" ' or ' 1=1 ", " ' or ' 1=1 ")
    # login_success('hanamaki', '123456')
    # login_success(" ' or ' 1=1 ", " ' or ' 1=1 ")

    # create_table()
    # insert_only('2', 1)

    # 如果操作的是多行插入 数据在传递的时候是 列表元组
    # list_data = [
    #     (3, 4),
    #     (4, 5)
    # ]
    # insert_many(list_data)
    # delete_by_id((1,))
    # update_by_id((3,))
    transaction(1, 2, 500)
